--    Author    : ChenErHao
--    Name      : EDW.AG_LOAB_INFO.HQL
--    Functions : 
--    Purpose   : Daily saving the variation data from ods
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-05-03  ChenErHao           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE EDW.AG_LOAB_INFO PARTITION (DATA_DATE = '#V_DATA_DATE#' ,DATA_SRC_ORG = '#V_DATA_SRC_ORG#')
SELECT 
    FIN_ORG_NO                                      -- 金融机构编码
    ,CLIENT_TYPE                                    -- 客户类型
    ,CLIENT_NO                                      -- 借款人代码
    ,CLIENT_INDUSTRY                                -- 贷款主体行业类别
    ,CLIENT_DIST                                    -- 借款人所在地区
    ,ECONOMY_TYPE                                   -- 企业出资人经济成分
    ,ENTERPRISE_SCALE                               -- 企业规模
    ,DUEBILL_NO                                     -- 贷款借据编码
    ,BUSINESS_TYPE                                  -- 贷款产品类别
    ,LOAN_INDUSTRY                                  -- 贷款实际投向
    ,DRAWDOWN_DATE                                  -- 贷款发放日期
    ,MATURITY_DATE                                  -- 贷款到期日期
    ,EXT_MATURITY_DATE                              -- 展期到期日期
    ,CCY                                            -- 贷款币种
    ,CASE WHEN LOAB.DATA_DATE = '#V_DATA_DATE#' THEN NVL(ACTUAL_BAL,0) ELSE 0 END AS ACTUAL_BAL -- 贷款余额
    ,IF_FLOAT                                       -- 利率是否固定
    ,RATE                                           -- 利率水平
    ,GUARANTY_TYPE                                  -- 贷款担保方式
    ,FIVE_CLASS                                     -- 贷款质量
    ,LOAN_STATUS                                    -- 贷款状态
    ,CASE 
        WHEN LOAB.DRAWDOWN_DATE IS NULL OR LOAB.DRAWDOWN_DATE='' THEN '7'
        -- 正常和逾期类期限
        WHEN LOAB.LOAN_STATUS <> 'FS02' AND (LOAB.MATURITY_DATE IS NULL OR LOAB.MATURITY_DATE = '') THEN '7'
        WHEN LOAB.LOAN_STATUS =  'FS02' AND (LOAB.EXT_MATURITY_DATE IS NULL OR LOAB.EXT_MATURITY_DATE = '') THEN '7'
        WHEN LOAB.LOAN_STATUS <> 'FS02' AND F_MONTHDIFF(LOAB.DRAWDOWN_DATE,LOAB.MATURITY_DATE) <=6   THEN '1'
        WHEN LOAB.LOAN_STATUS <> 'FS02' AND F_MONTHDIFF(LOAB.DRAWDOWN_DATE,LOAB.MATURITY_DATE) <=12  THEN '2'
        WHEN LOAB.LOAN_STATUS <> 'FS02' AND F_MONTHDIFF(LOAB.DRAWDOWN_DATE,LOAB.MATURITY_DATE) <=36  THEN '3'
        WHEN LOAB.LOAN_STATUS <> 'FS02' AND F_MONTHDIFF(LOAB.DRAWDOWN_DATE,LOAB.MATURITY_DATE) <=60  THEN '4'
        WHEN LOAB.LOAN_STATUS <> 'FS02' AND F_MONTHDIFF(LOAB.DRAWDOWN_DATE,LOAB.MATURITY_DATE) <=120 THEN '5'
        WHEN LOAB.LOAN_STATUS <> 'FS02' AND F_MONTHDIFF(LOAB.DRAWDOWN_DATE,LOAB.MATURITY_DATE) >120  THEN '6'
        -- 展期类期限
        WHEN LOAB.LOAN_STATUS = 'FS02' AND F_MONTHDIFF(LOAB.DRAWDOWN_DATE,LOAB.EXT_MATURITY_DATE) <=6   THEN '1'
        WHEN LOAB.LOAN_STATUS = 'FS02' AND F_MONTHDIFF(LOAB.DRAWDOWN_DATE,LOAB.EXT_MATURITY_DATE) <=12  THEN '2'
        WHEN LOAB.LOAN_STATUS = 'FS02' AND F_MONTHDIFF(LOAB.DRAWDOWN_DATE,LOAB.EXT_MATURITY_DATE) <=36  THEN '3'
        WHEN LOAB.LOAN_STATUS = 'FS02' AND F_MONTHDIFF(LOAB.DRAWDOWN_DATE,LOAB.EXT_MATURITY_DATE) <=60  THEN '4'
        WHEN LOAB.LOAN_STATUS = 'FS02' AND F_MONTHDIFF(LOAB.DRAWDOWN_DATE,LOAB.EXT_MATURITY_DATE) <=120 THEN '5'
        WHEN LOAB.LOAN_STATUS = 'FS02' AND F_MONTHDIFF(LOAB.DRAWDOWN_DATE,LOAB.EXT_MATURITY_DATE) >120  THEN '6'
        ELSE '7'
     END AS TERM_CODE                                -- 贷款期限
    ,CASE WHEN LOAB.DATA_DATE = '#V_DATA_DATE#' THEN NVL(ACTUAL_BAL,0)*NVL(RATE,0) ELSE 0 END     AS WSUM_BAL --余额加权值
    ,ORG.ORG_CODE_2 AS ORG_TYPE                      -- 机构类型
    ,CASE
        WHEN LOAB.DRAWDOWN_DATE IS NULL OR LOAB.DRAWDOWN_DATE='' THEN 'Z'
        WHEN LOAB.LOAN_STATUS <> 'FS02' AND (LOAB.MATURITY_DATE IS NULL OR LOAB.MATURITY_DATE = '') THEN 'Z'
        WHEN LOAB.LOAN_STATUS =  'FS02' AND (LOAB.EXT_MATURITY_DATE IS NULL OR LOAB.EXT_MATURITY_DATE = '') THEN 'Z'
        WHEN LOAB.LOAN_STATUS <> 'FS02' AND F_MONTHDIFF(LOAB.DRAWDOWN_DATE,LOAB.MATURITY_DATE) <=12  THEN 'A'
        WHEN LOAB.LOAN_STATUS <> 'FS02' AND F_MONTHDIFF(LOAB.DRAWDOWN_DATE,LOAB.MATURITY_DATE) >12  THEN 'B'
        WHEN LOAB.LOAN_STATUS = 'FS02' AND F_MONTHDIFF(LOAB.DRAWDOWN_DATE,LOAB.EXT_MATURITY_DATE) <=12  THEN 'A'
        WHEN LOAB.LOAN_STATUS = 'FS02' AND F_MONTHDIFF(LOAB.DRAWDOWN_DATE,LOAB.EXT_MATURITY_DATE) >12  THEN 'B'
        ELSE 'Z'
     END AS TERM_TYPE                                -- 期限类型
FROM ODS.SUST_DLMG_LOAB LOAB
LEFT JOIN DIMENSION.T_ORG_BIZ_LVL ORG ON(LOAB.FIN_ORG_NO=ORG.ORG_CODE_4)
WHERE LOAB.DATA_DATE = '#V_DATA_DATE#' AND LOAB.DATA_SRC_ORG = '#V_DATA_SRC_ORG#'
;